package com.yc.scott.dao;

import java.util.*;

import com.yc.jdbc.DBHelper;
import com.yc.jdbc.DBHelper.PageDTO;

// Dao => data access object 数据访问对象
public class EmpDao {
	
	public PageDTO select(
			String ename,  // 姓名  模糊查询
			String job,    // 岗位  精确查询
			int sal1, int sal2, // 工资  区间查询
			String hiredate1, String hiredate2,  // 日期 区间查询
			String dname,  // 部门名称  模糊查询
			int page,
			int size
			){ 
		/*
		 * select * from emp
		 * select * from emp where ename like ?
		 * select * from emp where ename like ? and job = ?
		 * select * from emp where job = ? and sal between ? and ?
		 * ...
		 * 如何实现组合条件查询?
		 * 1. 初始语句用 where 1=1 结尾, 方便拼接条件
		 * 2. 用List集合保存参数, 方便添加参数
		 */
		String sql = "select * from emp where 1=1";
		List<Object> params = new ArrayList<>();
		
		if(ename!=null && ename.isBlank() == false) {
			sql += " and ename like ?";
			params.add("%"+ename+"%");
		}
		
		if(job!=null && job.isBlank() == false) {
			sql += " and job = ?";
			params.add(job);
		}
		
		if(sal1 > 0) {
			sql += " and sal >= ?";
			params.add(sal1);
		}
		
		if(sal2 > 0) {
			sql += " and sal <= ?";
			params.add(sal2);
		}
		// 使用正则表达式判断日期格式是否正确
		if(hiredate1!=null && hiredate1.matches("\\d{4}-\\d{2}-\\d{2}")) {
			sql += " and hiredate >= to_date(?,'yyyy-mm-dd')";
			params.add(hiredate1);
		}
		if(hiredate2!=null && hiredate2.matches("\\d{4}-\\d{2}-\\d{2}")) {
			sql += " and hiredate <= to_date(?,'yyyy-mm-dd')";
			params.add(hiredate2);
		}
		
		if(dname!=null && dname.isBlank()==false) {
			sql += " and exists(select 1"
					+ "           from dept"
					+ "          where emp.deptno=dept.deptno"
					+ "            and dept.dname like ?)";
			params.add("%" + dname + "%");
		}
		
		return DBHelper.selectPage(sql, page, size, params.toArray());
	}
	
	
	public void insert(String ename, String job, Integer mgr,
			java.sql.Date hiredate, Integer sal, Integer comm, Integer deptno,
			String pwd) {
		String sql = "insert into emp values("
				+ "(select max(empno)+1 from emp),"
				+ "?,?,?,?,?,?,?,?)";
		DBHelper.update(sql, ename, job, mgr, hiredate, sal, comm, deptno,pwd);
	}
	
	public void update(Integer empno, String ename, String job, Integer mgr,
			java.sql.Date hiredate, Integer sal, Integer comm, Integer deptno,
			String pwd) {
		String sql = "update emp set"
				+ " ename = ?,"
				+ " job = ?,"
				+ " mgr = ?,"
				+ " hiredate = ?,"
				+ " sal = ?,"
				+ " comm = ?,"
				+ " deptno = ?,"
				+ " pwd = ?"
				+ " where empno = ?";
		DBHelper.update(sql, ename, job, mgr, hiredate, sal, comm, deptno, pwd, empno);
	}
	
	public Map<String,Object> selectByEmpno(String empno){
		return DBHelper.selectOne("select * from emp where empno=?", empno);
	}
	
//	public static void main(String[] args) {
//		EmpDao dao = new EmpDao();
//		System.out.println("=---------------------------------");
//		dao.select(null, null, 0, 0, null, null, null).forEach(System.out::println);
//		System.out.println("=---------------------------------");
//		dao.select("A", null, 0, 0, null, null, null).forEach(System.out::println);
//		System.out.println("=---------------------------------");
//		dao.select(null, "SALESMAN", 0, 0, null, null, null).forEach(System.out::println);
//		System.out.println("=---------------------------------");
//		dao.select("S", null, 1000, 0, null, null, null).forEach(System.out::println);
//		System.out.println("=---------------------------------");
//		dao.select("S", "MANAGER", 1000, 3000, null, null, null).forEach(System.out::println);
//		System.out.println("=---------------------------------");
//		dao.select("S", null, 1000, 0, null, null, null).forEach(System.out::println);
//		System.out.println("=---------------------------------");
//		dao.select(null, null, 0, 0, "1980-01-01", "1982-12-31", null).forEach(System.out::println);
//		System.out.println("=---------------------------------");
//		dao.select(null, null, 0, 0, null, null, "S").forEach(System.out::println);
//		System.out.println("=---------------------------------");
//	}

}
